import pymysql
from collections import Counter


class MovieChecker:
    def __init__(self):
        self.connect = None

    def open_mysql(self):
        try:
            self.connect = pymysql.connect(
                host='localhost',
                port=3306,
                user='root',
                password='123456',
                database='dodan_movie',
                charset='utf8mb4'
            )
            print('连接成功')
            return True
        except Exception as e:
            print("连接异常", e)
            return False

    def close_mysql(self):
        if self.connect is not None:
            self.connect.close()

    def execute_query(self, sql):
        if not self.open_mysql():
            return None
        try:
            with self.connect.cursor() as cursor:
                cursor.execute(sql)
                result = cursor.fetchall()#返回的是列表,都是列表里面是元组，二维采取的是第几个元组的第几个元素
                return result
        except Exception as e:
            print("查询失败", e)
        finally:
            self.close_mysql()
        return None

    def get_total_movies(self):
        sql = "SELECT COUNT(*) FROM dodan_movie"
        result = self.execute_query(sql)
        return result[0][0] if result else None

    def get_best_first_actor(self):
        sql = "SELECT first_actor FROM dodan_movie GROUP BY first_actor ORDER BY COUNT(*) DESC LIMIT 1"
        result = self.execute_query(sql)
        return result[0][0] if result else None
        # sql = "SELECT first_actor FROM dodan_movie WHERE first_actor IS NOT NULL"
        # result = self.execute_query(sql)
        # actors = [item[0] for item in result if item[0]]
        # counter = Counter(actors)
        # return counter.most_common(1)[0] if counter else None

    def get_most_comments_movie(self):
        sql = "SELECT movie_name FROM dodan_movie ORDER BY comments DESC LIMIT 10"
        result = self.execute_query(sql)
        return [row[0] for row in result] if result else []
    def get_best_rating_movie(self):
        sql='select movie_name,rating_num from dodan_movie order by rating_num desc limit 1'
        result = self.execute_query(sql)
        return result[0][0] if result else None
    def get_most_movie_country(self):
        sql='select country from dodan_movie'
        result = self.execute_query(sql)
        # return{ for i in result for j in i[0].split(' ') if i[0].strip()}if result else None
        return {
            country.strip()
            for item in result
            for country in item[0].split(' ')
            if country.strip()
        }#Python的集合类型基于哈希表实现，其迭代顺序取决于哈希值计算和存储机制24。
        # 即使包含相同元素，每次运行时内存分配差异会导致输出顺序变化14。
    


    def find_most_common_country(self):
        if not self:
            return None
        sql = 'select country from dodan_movie'
        data = self.execute_query(sql)
        result=[ country.strip()
            for item in data
            for country in item[0].split(' ')
            if country.strip()
                 ]
        # return data[0][0] if data else None
        # return  result
        # if not result:
        #     return None
        counter = Counter(result)
        # return [item[0].split(' ') for item in data for country in item[0].split(' ')]
        # # 返回出现次数最多的国家及其次数
        # return counter
        return counter.most_common() if result else None

    def find_movie_genre(self):
        sql='select genre from dodan_movie'
        data = self.execute_query(sql)
        result=[ genre.strip()
                 for item in data
                 for genre in item[0].split(' ')
                 if genre.strip()
                 ]
        counter = Counter(result)

        # # 返回出现次数最多的国家及其次数
        return counter if result else None


# 使用示例
if __name__ == "__main__":
    checker = MovieChecker()
    total_movies = checker.get_total_movies()
    best_first_actor = checker.get_best_first_actor()
    most_commented_movies = checker.get_most_comments_movie()
    best_rating_movie = checker.get_best_rating_movie()
    most_movie_country =checker.get_most_movie_country()
    most_common_country = checker.find_most_common_country()
    movie_genre = checker.find_movie_genre()


    print(f"总电影数: {total_movies}")
    print(f"最佳主演: {best_first_actor}")
    print("评论数最多的10部电影:")
    for movie in most_commented_movies:
        print(movie)
    print(f"最佳电影: {best_rating_movie}")
    # print(len(most_movie_country))
    print(f"电影产出国家: {most_movie_country}")
    print(f"各地电影产出数量: {most_common_country}")
    print(f"电影类型: {movie_genre}")